package com.wanmait.cookbook.dao;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;

import com.wanmait.cookbook.vo.Cookbook;
import com.wanmait.cookbook.vo.IngredientsConsumption;
import com.wanmait.cookbook.vo.RecipeTypeSecond;
import com.wanmait.cookbook.vo.Userinfo;
import com.wanmait.demo.util.DBHelper;

/**
 * ʳ������DAO
 * @author 17297
 *
 */
public class IngredientsConsumptionDAO {
	private IngredientsConsumptionDAO() {}
	private static IngredientsConsumptionDAO ingredientsConsumptionDAO = new IngredientsConsumptionDAO();
	public static IngredientsConsumptionDAO getIngredientsConsumptionDAO() {
		return ingredientsConsumptionDAO;
	}
	  /**
	   *  根据 食谱id 查找食材用量
	   * @param id
	   * @return
	   */
	  public List<IngredientsConsumption> findConsumptionByCookbookId(Integer id) {
	    List<IngredientsConsumption> ingredientsConsumptions = new ArrayList<IngredientsConsumption>();
	    IngredientsConsumption ingredientsConsumption = null;
	    Connection conn = null;
	    PreparedStatement ps = null;
	    ResultSet rs = null;
	    String sql = "SELECT * FROM ingredients_consumption WHERE cookbook_id=?";
	    try {
	      conn = DBHelper.getHelper().getConn();
	      ps = conn.prepareStatement(sql);
	      ps.setInt(1, id);
	      rs = ps.executeQuery();
	      while (rs.next()) {
	        ingredientsConsumption = new IngredientsConsumption();
	        Cookbook cookbook = new Cookbook();
	        cookbook.setId(rs.getInt("cookbook_id"));
	        ingredientsConsumption.setId(rs.getInt("id"));
	        ingredientsConsumption.setCookbook(cookbook);
	        ingredientsConsumption.setIngredientsName(rs.getString("ingredients_name"));
	        ingredientsConsumption.setDosage(rs.getString("dosage"));
	        ingredientsConsumptions.add(ingredientsConsumption);
	      }
	    } catch (SQLException e) {
	      // TODO Auto-generated catch block
	      e.printStackTrace();
	    }
	    finally {
	      DBHelper.getHelper().closeConn(rs, ps, conn);
	    }
	    return ingredientsConsumptions;
	  }
	  /**
	   *  修改食材用量
	   * @param ingredientsConsumption
	   */
	  public void updateIngredients(IngredientsConsumption ingredientsConsumption) {
	    String sql = "UPDATE ingredients_consumption SET ingredients_name=?,dosage=? WHERE id=?";
	    DBHelper.getHelper().executeUpdate(sql, ingredientsConsumption.getIngredientsName(),ingredientsConsumption.getDosage(),ingredientsConsumption.getId());
	  }
	  /**
	   *  草稿新添加食材用量
	   * @param ingredientsConsumption
	   */
	  public void addIngredientsNew(IngredientsConsumption ingredientsConsumption) {
	    String sql = "INSERT INTO ingredients_consumption(cookbook_id,ingredients_name,dosage) values(?,?,?)";
	    DBHelper.getHelper().executeUpdate(sql, ingredientsConsumption.getCookbook().getId(),ingredientsConsumption.getIngredientsName(),ingredientsConsumption.getDosage());
	  }

	  
	 /**
	   *  添加食材用量
	   * @param ingredientsConsumption
	   */
	  public void addIngredients(IngredientsConsumption ingredientsConsumption) {
	    String sql = "INSERT INTO ingredients_consumption(cookbook_id,ingredients_name,dosage) select MAX(id),?,? from cookbook";
	    DBHelper.getHelper().executeUpdate(sql, ingredientsConsumption.getIngredientsName(),ingredientsConsumption.getDosage());
	  }

	//点击食材查找食谱
	public List<Cookbook> foodToCookbookOnepager(String name){
		List<Cookbook> foodToCookbooks=new ArrayList<>();
		Cookbook foodToCookbook=null;
		Connection conn=null;
		Userinfo userinfo=null;
		
		PreparedStatement  ps=null;
		ResultSet rs=null;
		try {
			String sql="SELECT *  FROM cookbook,userinfo," + 
					" (SELECT distinct cookbook_id AS idd  "+ 
					" FROM ingredients_consumption WHERE ingredients_name like ?) AS temp" + 
					" WHERE temp.idd=cookbook.id AND userinfo.id=cookbook.userinfo_id";
			
			conn=DBHelper.getHelper().getConn();
			ps=conn.prepareStatement(sql);
			ps.setString(1, "%"+name+"%");
			rs=ps.executeQuery();
			while(rs.next()) {
				foodToCookbook = new Cookbook();
				userinfo = new Userinfo();
				
				foodToCookbook.setCookingDifficulty(rs.getInt("cooking_difficulty"));
				foodToCookbook.setCookingMethod(rs.getString("cooking_method"));
				foodToCookbook.setCookingTime(rs.getInt("cooking_time"));
				foodToCookbook.setDraftBox(rs.getBoolean("draft_box"));
				foodToCookbook.setEnable(rs.getBoolean("cookbook.enable"));
				foodToCookbook.setHot(rs.getBoolean("cookbook.hot"));
				foodToCookbook.setId(rs.getInt("cookbook.id"));
				foodToCookbook.setIndexShow(rs.getBoolean("index_show"));
				foodToCookbook.setInfo(rs.getString("cookbook.info"));
				foodToCookbook.setInputTime(rs.getTimestamp("cookbook.input_time"));
				foodToCookbook.setPageviews(rs.getInt("pageviews"));
				foodToCookbook.setPic(rs.getString("cookbook.pic"));
				
				foodToCookbook.setStatus(rs.getBoolean("cookbook.status"));
				foodToCookbook.setTaste(rs.getString("taste"));
				foodToCookbook.setTips(rs.getString("tips"));
				foodToCookbook.setTitle(rs.getString("cookbook.title"));
				userinfo.setId(rs.getInt("userinfo_id"));
				userinfo.setAvatar(rs.getString("userinfo.avatar"));
				userinfo.setName(rs.getString("userinfo.name"));
				foodToCookbook.setUserinfo(userinfo);
				foodToCookbook.setVideo(rs.getString("video"));
				foodToCookbooks.add(foodToCookbook);
			}
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		finally {
			DBHelper.getHelper().closeConn(rs, ps, conn);
		}
		return foodToCookbooks;
		
	}

	//ͨ��ʳ��ID����ʳ������
	public List<IngredientsConsumption> findIngredientsConsumptionByCookbookId(int id) {
		List<IngredientsConsumption> ingredientsConsumptions = new ArrayList<IngredientsConsumption>();
		Connection conn = null;
		PreparedStatement ps = null;
		ResultSet rs= null;
		
		String sql="SELECT * FROM ingredients_consumption WHERE cookbook_id=?";
		try {
			conn=DBHelper.getHelper().getConn();
			ps=conn.prepareStatement(sql);
			ps.setInt(1, id);
			rs=ps.executeQuery();
			IngredientsConsumption ingredientsConsumption = null;
			while(rs.next()) {
				ingredientsConsumption =new IngredientsConsumption();
				Cookbook cookbook = new Cookbook();
				ingredientsConsumption.setId(rs.getInt("id"));
				ingredientsConsumption.setIngredientsName(rs.getString("ingredients_name"));
				ingredientsConsumption.setDosage(rs.getString("dosage"));
				cookbook.setId(id);
				ingredientsConsumption.setCookbook(cookbook);
				ingredientsConsumptions.add(ingredientsConsumption);
			}
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}finally {
			DBHelper.getHelper().closeConn(rs, ps, conn);
		}
		
		return ingredientsConsumptions;
	}
}
